import os, time, re, requests, json, sqlite3, pandas as pd, warnings, xlwings as xw, smtplib, schedule,base64, ddddocr,email  # 导入必要模块
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.header import Header
from seleniumwire import webdriver



pd.set_option('display.max_columns', 10)
warnings.simplefilter("ignore")

global LOG_FILENAME, sess, user_agent, download_file_dir, data_store, sleep_time, template_file, contact_file, contact_dict, report_generate,contact_file2,contact_dict2,email_address,G_driver_path, G_wd,account_info,auth
sleep_time = 0.2


class MySqlitDb(object):
    """Sqlite3 数据库操作类"""

    def __init__(self, dbname='test.db'):
        self.dbname = dbname
        self.con = None
        self.curs = None

    def getCursor(self):
        self.con = sqlite3.connect(self.dbname)
        if self.con:
            self.curs = self.con.cursor()

    def closeDb(self):
        if self.curs:
            self.curs.close()
        if self.con:
            self.con.commit()
            self.con.close()

    def __enter__(self):
        self.getCursor()
        return self.curs

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_val:
            write_log('Exception has generate:', exc_val)
            write_log('Sqlite3 execute error!')
        self.closeDb()


class sqliteDB(object):
    """定义数据库、数据表和数据查询等"""

    def __init__(self, dbname='test.db'):
        self.dbname = dbname

    def initDbAndTables(self, sql_script_create_tables=""):
        """init the database and create tables
            int -- integer
            str -- text
            bytes -- blob
            None -- NULL
        """
        if not os.path.exists(self.dbname):
            with MySqlitDb(self.dbname) as db:
                db.executescript(sql_script_create_tables)

    def opera_DB(self, sql_script_opreate_db, param_tuple):
        """universal operate database insert delete update
        """
        try:
            with MySqlitDb(self.dbname) as db:
                db.execute(sql_script_opreate_db, param_tuple)
            return True
        except:
            return False

    def opera_DB2(self, sql_script_opreate_db):
        """universal operate database insert delete update
        """
        try:
            with MySqlitDb(self.dbname) as db:
                db.execute(sql_script_opreate_db)
            return True
        except:
            return False

    def opera_DB_Insert_Many(self, sql_script_insert_many, param_tuple):
        """operate database insert many

         "insert into table_name (name,%s) values(?,%s)" % (','.join(keys))
        """
        try:
            with MySqlitDb(self.dbname) as db:
                db.executemany(sql_script_insert_many, param_tuple)
            return True
        except:
            return False

    def query_DB(self, script=''):
        """operate database select
         "select id from table_name where name = ?" ,('张尧',)
        """
        try:
            res = []
            with MySqlitDb(self.dbname) as db:
                db.execute(script)
                res = list(db.fetchall())
            return res
        except:
            return []


def init_environment():
    """该函数用于初始化环境，比如初始化全局遍历，初始化文件夹等"""
    global LOG_FILENAME, sess, user_agent, download_file_dir, data_store, sleep_time, template_file, contact_file, contact_dict, report_generate,contact_file2,contact_dict2,email_address,G_driver_path, G_wd,account_info,auth
    G_driver_path = os.getcwd() + r'\driver\chromedriver.exe'  # 指定浏览器驱动的路径
    LOG_FILENAME = f'.{os.sep}log.log'  # 定义log文件的路径
    sess = requests.session()  # 初始化session并清除cookies，定义UA
    sess.cookies.clear()
    contact_dict2 = {}
    email_info = f'.{os.sep}account.txt'
    email_address = ''
    try:
        with open(email_info, encoding='utf8') as f:
            info = f.read().split()
            email_address = info[:3]
            account_info = info[3:]
    except:
        with open(email_info, encoding='gbk') as f:
            info = f.read().split()
            email_address = info[:3]
            account_info = info[3:]
    if not email_address:
        assert('邮箱信息不对,请检查邮箱信息')
        write_log('邮箱信息不对')
    user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.4758.102 Safari/537.36 Edg/98.0.1108.55'
    # 设定一个默认的文件下载路径
    if not os.path.exists(f'.{os.sep}文件下载'):
        os.makedirs(f'.{os.sep}文件下载')
    # 设定默认的模板路径
    if not os.path.exists(f'.{os.sep}template'):
        os.makedirs(f'.{os.sep}template')
    # 设定特定的日报生成结果路径
    if not os.path.exists(f'.{os.sep}日报生成结果'):
        os.makedirs(f'.{os.sep}日报生成结果')
    download_file_dir = f'.{os.sep}文件下载'
    template_file = f'.{os.sep}template{os.sep}template.xlsx'
    contact_file = f'.{os.sep}template{os.sep}通讯录.txt'
    contact_file2 = f'.{os.sep}template{os.sep}通讯录2.txt'
    report_generate = f'.{os.sep}日报生成结果{os.sep}'
    # 加载通讯录字典，json文件里保存的是公司所有网络口维护人员的联系方式，通过匹配号码来确定割接的区县
    with open(contact_file, 'r') as f:
        contact_dict = json.loads(f.read())
    with open(contact_file2, 'r',encoding='utf8') as f:
        for line in f.readlines():
            info = line.strip().split(',')
            contact_dict2[info[0]] = info[1]

    data_store = sqliteDB('CMCC.db')  # 创建数据库，数据库的名字叫做CMCC
    # 根据需要创建数据表
    data_store.initDbAndTables(
        """create table cutOverOrders(id integer primary key autoincrement not null,orderSn text default null,orderSubject text default null, province text default null, city text default null, profession text default null,startTime text default null, finishTime text default null,optstartTime text default null,testfinishTime text default null,cuttype text default null,cutlevel text default null,opttype text default null,systemtype text default null,netnum text default null,netverdor text default null,equipmentname text default null,manager text default null, managerphone text default null,operatortype text default null,managersigm text default null,finishresult text default null,stillnotstop text default null,reason text default null,note text default null,state text default null, cutreportname text default null,cutreportTime text default null,finishresultreportTime text default null,cutcontent text default null, effectgovernment text default null,cutprepare text default null, cutmake text default null, test text default null,ncGrpMembers text default null, auditmessage text default null,pointTime text default null,analyzeTime text default no,qualified text default null,orderjudge text default null);
        create table dayResult(id integer primary key autoincrement not null,region text default null,orderSn text default null,orderSubject text default null, province text default null, city text default null, profession text default null,startTime text default null, finishTime text default null,optstartTime text default null,testfinishTime text default null,cuttype text default null,cutlevel text default null,opttype text default null,systemtype text default null,netnum text default null,netverdor text default null,equipmentname text default null,manager text default null, managerphone text default null,operatortype text default null,managersigm text default null,finishresult text default null,stillnotstop text default null,reason text default null,note text default null,state text default null, cutreportname text default null,cutreportTime text default null,finishresultreportTime text default null,cutcontent text default null, effectgovernment text default null,cutprepare text default null, cutmake text default null, test text default null);
        create table daySum(id integer primary key autoincrement not null,execTime text default null,region1 text default null,,region1 text default null,region2 text default null,region3 text default null,region4 text default null,region5 text default null,region6 text default null,region7 text default null,region8 text default null,region9 text default null,region10 text default null,region11 text default null,region12 text default null,region13 text default null,region14 text default null,region15 text default null,region16 text default null
,region17 text default null,region18 text default null,region19 text default null,region20 text default null,region21 text default null,region22 text default null,region23 text default null,region24 text default null,region25 text default null,region26 text default null,region27 text default null,region28 text default null,region29 text default null,region30 text default null,region31 text default null,region32 text default null
,region33 text default null,region34 text default null,region35 text default null,region36 text default null,region37 text default null,region38 text default null,region39 text default null,region40 text default null,region41 text default null,region42 text default null,region43 text default null,region44 text default null,region45 text default null,region46 text default null,region47 text default null,region48 text default null
,region49 text default null,region50 text default null,region51 text default null,region52 text default null,region53 text default null,region54 text default null,region55 text default null,region56 text default null,region57 text default null,region58 text default null,region59 text default null,region60 text default null,region61 text default null,region62 text default null,region63 text default null,region64 text default null
,region65 text default null,region66 text default null,region67 text default null,region68 text default null,region69 text default null,region70 text default null,region71 text default null,region72 text default null,region73 text default null,region74 text default null,region75 text default null,region76 text default null,region77 text default null,region78 text default null,region79 text default null,region80 text default null
,region81 text default null,region82 text default null,region83 text default null,region84 text default null,region85 text default null,region86 text default null,region87 text default null,region88 text default null,region89 text default null,region90 text default null,region91 text default null,region92 text default null,region93 text default null,region94 text default null,region95 text default null,region96 text default null
,region97 text default null,region98 text default null,region99 text default null,region100 text default null,region101 text default null,region102 text default null,region103 text default null,region104 text default null,region105 text default null,region106 text default null,region107 text default null,region108 text default null,region109 text default null,region110 text default null,region111 text default null,region112 text default null
,region113 text default null,region114 text default null,region115 text default null,region116 text default null,region117 text default null,region118 text default null,region119 text default null,region120 text default null,region121 text default null,region122 text default null,region123 text default null,region124 text default null,region125 text default null,region126 text default null,region127 text default null,region128 text default null,region129 text default null
,region130 text default null,region131 text default null,region132 text default null,region133 text default null,region134 text default null,region135 text default null,region136 text default null,region137 text default null,region138 text default null,region139 text default null,region140 text default null,region141 text default null,region142 text default null,region143 text default null,region144 text default null,region145 text default null,region146 text default null
,region147 text default null,region148 text default null,region149 text default null,region150 text default null,region151 text default null,region152 text default null,region153 text default null,region154 text default null,region155 text default null,region156 text default null,region157 text default null,region158 text default null,region159 text default null,region160 text default null
,region161 text default null,region162 text default null,region163 text default null,region164 text default null,region165 text default null,region166 text default null,region167 text default null,region168 text default null,region169 text default null,region170 text default null,region171 text default null,region172 text default null,region173 text default null,region174 text default null,region175 text default null,region176 text default null
,region177 text default null,region178 text default null,region179 text default null,region180 text default null,region181 text default null,region182 text default null,region183 text default null,region184 text default null,region185 text default null,region186 text default null,region187 text default null,region188 text default null,region189 text default null,region190 text default null,region191 text default null,region192 text default null
,region193 text default null,region194 text default null,region195 text default null,region196 text default null,region197 text default null,region198 text default null,region199 text default null,region200 text default null,region201 text default null,region202 text default null,region203 text default null,region204 text default null,region205 text default null,region206 text default null,region207 text default null,region208 text default null
,region209 text default null,region210 text default null,region211 text default null,region212 text default null,region213 text default null,region214 text default null,region215 text default null,region216 text default null,region217 text default null,region218 text default null,region219 text default null,region220 text default null,region221 text default null,region222 text default null,region223 text default null,region224 text default null
,region225 text default null,region226 text default null,region227 text default null,region228 text default null,region229 text default null,region230 text default null,region231 text default null,region232 text default null,region233 text default null,region234 text default null,region235 text default null,region236 text default null,region237 text default null,region238 text default null,region239 text default null,region240 text default null
,region241 text default null,region242 text default null,region243 text default null)""")  # 初始化建立需要的数据表


def write_log(log_content):
    """该函数用户记录日志并显示日志，如果不需要显示日志，可以把print注释掉"""
    global LOG_FILENAME
    with open(LOG_FILENAME, 'a+', encoding='utf-8') as f:
        f.write(
            f"{time.strftime('%Y{y}%m{m}%d{d}-%H{h}%M{f}%S{s}').format(y='年', m='月', d='日', h='时', f='分', s='秒')} log: {log_content}\n")
        print(
            f"{time.strftime('%Y{y}%m{m}%d{d}-%H{h}%M{f}%S{s}').format(y='年', m='月', d='日', h='时', f='分', s='秒')} log: {log_content}\n")


def ocr_vercode(vercode_url):
    """识别图片验证码并返回识别后的结果"""
    try:
        data = vercode_url.split(',')[1]
        image_data = base64.b64decode(data)
        vercode_ocr = ddddocr.DdddOcr()
        vercode_text = vercode_ocr.classification(image_data)
    except:
        vercode_text = ''
        write_log('验证码识别函数异常')
    return vercode_text  # 返回文字识别结果

# 需要先登录
def login_func(uname, passwd):
    """通过用户名密码登录系统"""
    # 打开首页并登录
    login_success_flag = False
    global G_wd, sess, auth
    try:
        G_wd = webdriver.Chrome(G_driver_path)
        G_wd.implicitly_wait(5)
        index_url = 'http://183.207.103.75:8000/test/cut/#/login'
        G_wd.get(index_url)
        uname_element = G_wd.find_element_by_xpath( '//input[@placeholder="用户名"]')
        uname_element.clear()
        uname_element.send_keys(uname)
        # 2.获取密码输入框输入密码
        passwd_element = G_wd.find_element_by_xpath( '//input[@placeholder="密码"]')
        passwd_element.clear()
        passwd_element.send_keys(passwd)
        # 3.获取图片验证码，调用验证码识别函数进行识别并返回结果
        vercode_element = G_wd.find_element_by_xpath( '//input[@placeholder="验证码"]/../../img[1]')
        vercode_url = vercode_element.get_attribute('src')  # 获取图片验证码的地址，这里应该只是一个base64数据
        vercode_text = ocr_vercode(vercode_url)
        vercode_input_element = G_wd.find_element_by_xpath( '//input[@placeholder="验证码"]')
        vercode_input_element.clear()
        vercode_input_element.send_keys(vercode_text)
        # 4.点击登录按钮
        login_button = G_wd.find_element_by_xpath( '//button[@class="el-button submit_btn el-button--primary"]')
        login_button.click()
        # 如果登录成功，则能够看到菜单栏
        menubar = G_wd.find_elements_by_xpath('//li[@role="menuitem"]')
        for i in range(50):
            for req in G_wd.requests:
                if 'access_token' in req.url:
                    write_log('本次登录成功')
                    login_success_flag = True
                    login_cookies = G_wd.get_cookies()
                    cookies_jar = requests.cookies.RequestsCookieJar()
                    for cookie in login_cookies:
                        cookies_jar.set(cookie['name'], cookie['value'])
                    sess.cookies = cookies_jar
                    auth =  'bearer '  + req.url[req.url.find('access_token')+len('access_token')+1:]
                    # print(auth)
                    G_wd.quit()
                    break
            else:
                time.sleep(0.2)
            if login_success_flag:
                break
    except Exception as err:
        write_log(f'本次登录结果异常，请排查原因:{err}')
        login_success_flag = False
        G_wd.quit()
    finally:
        return login_success_flag


def login_final_fun(uname, passed, try_num=6):
    """如果登录不成功，则多试几次，肯定可以登录成功,默认5次"""
    for i in range(try_num):
        if login_func(uname, passed):
            break
        else:
            time.sleep(sleep_time)
    else:
        write_log("超出最大尝试次数，仍然没有登录成功，请联系技术人员排查原因")



def get_cutover_orders(days=20):
    """获取指定天数内的割接工单编号并导出，默认为20天，后续存入数据库中或Excel表格中"""
    # 获取开始时间和结束时间
    global sess, user_agent
    time_ends_time = time.strftime('%Y-%m-%d %H:%M:%S')  # 获取当前时间点
    time_begins_time = time.strftime('%Y-%m-%d %H:%M:%S',
                                     time.localtime(time.time() - 3600 * 24 * days))  # 根据需求为20天之前时间点
    # 1. 先用此链接查询指定时间段内是否存在割接，如果没有的话，返回的json中total为0
    url = 'http://183.207.103.75:8000/cdispatching/cutoperate/v1/operate/queryTodoTaskList'
    data = {
        "orderNo": "",
        "specialty": "",
        "cutClass": "",
        "neType": "",
        "neName": "",
        "phaseName": "",
        "managerAcount": "",
        "cutNo": "",
        "cutResult": "",
        "province": "",
        'devAddr': "",
        "planStartTime": f'{time_begins_time};{time_ends_time}',
        "pageShowSize": 10,
        "currenPageNum": 1,
        "totalCount": 128,
        "bigRegion": "",
        "isShowAll": False,
        "impactEnterprise": ""
    }
    try:
        res = sess.post(url=url, json=data, headers={'User-Agent': user_agent, 'Host': '183.207.103.75:8000',
                                                     'Origin': 'http://183.207.103.75:8000',
                                                     'Refer': 'http://183.207.103.75:8000/test/cut/','Authorization':auth})
        res_json = json.loads(res.content.decode())  # 返回查询结果
        total = res_json.get('body').get('total')  # 如果返回结果为0，则代表没有查到，直接返回
        if not total:
            return False
    except Exception as err:
        write_log(f'查询割接列表出错:{err}')
        return False
    url = 'http://183.207.103.75:8000/cdispatching/cutoperate/v1/operate/exportCutTaskList'
    data = {
        "orderNo": "",
        "specialty": "",
        "cutClass": "",
        "neType": "",
        "neName": "",
        "phaseName": "",
        "managerAcount": "",
        "cutNo": "",
        "cutResult": "",
        "province": "",
        "planStartTime": f'{time_begins_time};{time_ends_time}',
        "pageShowSize": 10,
        "currenPageNum": 1,
        "totalCount": 0,
        "bigRegion": "",
        "isShowAll": False,
        "impactEnterprise": "",
        "cutLevels": [],
        "impactGraphicInfo": [],
        "devAddr": ""
    }
    try:
        res = sess.post(url=url, json=data, headers={'User-Agent': user_agent, 'Host': '183.207.103.75:8000',
                                                     'Origin': 'http://183.207.103.75:8000',
                                                     'Refer': 'http://183.207.103.75:8000/test/cut/','Authorization':auth})
        with open(download_file_dir + f'{os.sep}cutoverOrder.xlsx', 'wb') as f:  # 将所有割接工单编号存入excel表格中
            f.write(res.content)
        return True
    except Exception as err:
        write_log(f'导出割接列表出错:{err}')
        return False


def order_number_info(orderSn):
    """根据输入的工单编号如工单列表路径查询，返回需要的最终查询结果"""
    global sess, user_agent
    # 1.根据工单编号orderSn获取orderId,和割接ID applicantId
    index = 'http://183.207.103.75:8000/cdispatching/cutplan/v1/cutplan/querySelfOrder'
    data = {
        "currentPage": 1,
        "pageSize": 10,
        "orderSn": orderSn,
        "orderPhase": -1
    }
    try:
        res = sess.post(url=index, json=data, headers={'User-Agent': user_agent, 'Host': '183.207.103.75:8000',
                                                       'Origin': 'http://183.207.103.75:8000',
                                                       'Refer': 'http://183.207.103.75:8000/test/cut/','Authorization':auth})
        res_json = json.loads(res.content.decode())
        orderId = res_json.get('body').get('list')[0].get('orderId')
        applicantId = res_json.get('body').get('list')[0].get('applicantId')
    except Exception as err:
        write_log(f'获取orderId失败:{err}')
        return '', '', '',''
    # 2.根据orderId获取cutId,和ncGrpMembers(割接人员角色配置)
    index = 'http://183.207.103.75:8000/cdispatching/cutplan/v1/cutplan/queryOrder'
    params = {
        'orderID': orderId
    }
    try:
        res = sess.get(url=index, params=params, headers={'User-Agent': user_agent, 'Host': '183.207.103.75:8000',
                                                          'Origin': 'http://183.207.103.75:8000',
                                                          'Refer': 'http://183.207.103.75:8000/test/cut/','Authorization':auth})
        res_json = json.loads(res.content.decode())
        ncGrpMembers = res_json.get('body').get('cuttingDtoList')[0].get('ncGrpMembers')
        cutRoleDict = {1: '割接小组组长', 2: '割接操作人员', 3: '监督审核人员', 4: '网络监控人员', 5: '验证测试人员', 6: '割接随工人员', 7: '割接保障人员'}
        ncGrpMembers = [f"{mem.get('userName')}-{cutRoleDict.get(mem.get('cutRole'))}" for mem in ncGrpMembers]
        ncGrpMembers = '@'.join(ncGrpMembers)
    except Exception as err:
        write_log(f'获取割接小组成员角色失败:{err}')
        return '', '', '',''
    # 3.根据oderId获取审批信息auditmessage
    index = 'http://183.207.103.75:8000/cdispatching/cutplan/v1/cutplan/queryAuditOrder'
    params = {
        'orderID': orderId
    }
    try:
        res = sess.get(url=index, params=params, headers={'User-Agent': user_agent, 'Host': '183.207.103.75:8000',
                                                          'Origin': 'http://183.207.103.75:8000',
                                                          'Refer': 'http://183.207.103.75:8000/test/cut/','Authorization':auth})
        res_json = json.loads(res.content.decode())
        auditmessageDict = {'割接计划制定': '创建了割接工单', '割接方案制定': '提交了割接方案', '割接方案会审': '会审完成', '一级审批': '审批完成'}
        auditmessage = res_json.get('body').get('auditMessage').get('auditmessage')
        auditmessage = [
            f"{message.get('userName')}-{auditmessageDict.get(message.get('taskName'))}-{message.get('note')}" for
            message in auditmessage]
        auditmessage = '@'.join(auditmessage)
        cutID = res_json.get('body').get('cuttings')[0].get('cutID')
        # return ncGrpMembers, auditmessage  # 返回割接人员信息，审批流程及意见
    except Exception as err:
        write_log(f'获取流程审批信息失败:{err}')
        return '', '', '',''
    try:
        index = f'http://183.207.103.75:8000/cdispatching/cutoperate/v1/operate/queryCutProcessProgress/{cutID}'
        res = sess.get(url=index, headers={'User-Agent': user_agent, 'Host': '183.207.103.75:8000',
                                           'Origin': 'http://183.207.103.75:8000',
                                           'Refer': 'http://183.207.103.75:8000/test/cut/','Authorization':auth})
        res_json = json.loads(res.content.decode())
        pointTimeList = res_json.get('body').get('phase_list')  # 割接的各个环节时间点，分别是 割接准备、割接实施、测试验证、结果反馈、运行观察、割接完成  获取的是个列表
        pointTimeList = [
            f"{item.get('plan_start_time')}@{item.get('plan_finish_time')}@{item.get('actual_start_time')}@{item.get('actual_finish_time')}"
            for item in pointTimeList]
        pointTimeStr = '^'.join(pointTimeList)  # 生成一个时间字符串
        return ncGrpMembers, auditmessage, pointTimeStr, applicantId
    except Exception as err:
        write_log(f'获取割接各个环节时间失败:{err}')
        return '', '', '',''


def analyzeOrdersAndStore(filePath):
    """打开存储的excel工单表格，根据完成情况来进行分析，只有徐州的割接需要导入系统，如果这个工单编号在数据库中已存在了，就直接丢弃吧，否则要去查具体的信息"""
    global data_store, sleep_time
    # 首先打开filepath的excel文件，提取其中的全部数据
    df = pd.read_excel(filePath, dtype=str,engine='xlrd')
    # 逐行遍历dataframe，然后查询是否存在于数据库中，如果已经有了，则不执行插入，否则插入一个新的数据
    # 为了提高按行遍历的效率，使用itertuples（）
    # df['割接成员角色'],df['割接审批流程'] = df['工单编号'].apply(lambda x:order_number_info(x)[0]),df['工单编号'].apply(lambda x:order_number_info(x)[1])
    df = df[df['地市'] == '徐州市']
    for idx, row in df.iterrows():
        # 查询orderSn在数据库中是否已存在
        if not data_store.query_DB(f'select * from cutOverOrders where orderSn = "{row.工单编号}"'):
            # write_log(f'当前{row.工单编号}不存在，执行插入操作')
            data_store.opera_DB_Insert_Many(
                'insert into cutOverOrders(orderSn,orderSubject, province, city, profession,startTime, finishTime,optstartTime,testfinishTime,cuttype,cutlevel,opttype,systemtype,netnum,netverdor,equipmentname,manager, managerphone,operatortype,managersigm,finishresult,stillnotstop,reason,note,state, cutreportname,cutreportTime,finishresultreportTime,cutcontent, effectgovernment,cutprepare, cutmake, test,ncGrpMembers, auditmessage,pointTime)  values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
                [(row.工单编号, row.割接编号, row.省份, row.地市, row.专业, row.割接开始时间, row.割接结束时间, row.割接实施开始时间, row.测试验证结束时间,
                  row.割接类型, row.割接级别, row.操作类型, row['网元/系统类型'], row['网元/设备数量'], row['网元/设备厂家'], row['网元/设备名称'],
                  row['责任领导(部门)'], row.责任领导电话, row.操作人员类型, row.是否有领导签字, row.完成情况, row.是否有遗留, row.原因说明, row.遗留问题说明,
                  row.状态, row.割接上报人, row.割接计划上报时间, row.完成情况上报时间, row.割接内容描述, row.是否影响政企,
                  row.割接准备, row.割接实施, row.测试验证, order_number_info(row.工单编号)[0], order_number_info(row.工单编号)[1],
                  order_number_info(row.工单编号)[2])])
            time.sleep(sleep_time)
        else:
            pass
            # write_log(f'当前{row.工单编号}已存在，无需操作')


def futchAndStoreDatas(Ndays=20):
    """调用其他函数实现功能"""
    global download_file_dir
    # 1.获取割接列表,存入数据库
    write_log('开始提取数据')
    if get_cutover_orders(Ndays):
        analyzeOrdersAndStore(download_file_dir + f'{os.sep}cutoverOrder.xlsx')
        write_log('已完成本次数据提取，提取结果成功！')
        return True
    else:
        write_log('已完成本次数据提取，提取结果失败，请核查原因！')
        return False


def orderQualified(orderTuple):
    """判断工单是否合格，具体合格的要求根据省公司要求来"""
    # 1.如果 “ 完成情况  finishresult” 为 取消，则只需要评估割接小组成员和审批流程是否 符合规定 即可
    global email_address
    qualified_flag = 1
    judgeOrder = ""
    ncGrpMembers = orderTuple[34]
    auditmessage = orderTuple[35]
    pointTime = orderTuple[36]
    if orderTuple[21] == '取消':
        qualified_flag = 0
        judgeOrder += '@割接取消'
        if ncGrpMembers:
            # 割接小组成员  郑子健-监督审核人员@于永年-割接小组组长@吴宣东-网络监控人员@单晓芳-割接随工人员@单晓芳-割接操作人员@单晓芳-验证测试人员@闫彬-割接保障人员
            ncGrpMembers = ncGrpMembers.split('@')
            ncGrpMembers = [mem.split('-') for mem in ncGrpMembers]
            ncGrpMembersDict = {mem[1]: mem[0] for mem in ncGrpMembers}
            if len({ncGrpMembersDict['割接小组组长'], ncGrpMembersDict['割接操作人员'], ncGrpMembersDict['割接保障人员']}) < 3 or len(
                    {ncGrpMembersDict['监督审核人员'], ncGrpMembersDict['割接小组组长'], ncGrpMembersDict['网络监控人员'],
                     ncGrpMembersDict['割接随工人员'], ncGrpMembersDict['割接操作人员'], ncGrpMembersDict['验证测试人员'],
                     ncGrpMembersDict['割接保障人员']}) < 4:
                judgeOrder += '@割接小组成员不规范'
                qualified_flag = 0
        if auditmessage:
            # 审批流程     于永年-创建了割接工单-None@于永年-提交了割接方案-None@吴宣东-会审完成-@田东阳-审批完成-@杨尚涛-None-短信审批:同意
            # 陈海良 - 创建了割接工单 - None @ 陈海良 - 提交了割接方案 - None @ 陈海良 - 会审完成 - @陈海良 - 审批完成 - @徐江 - None -
            auditmessage = auditmessage.split('@')
            auditmessage = [audit.split('-') for audit in auditmessage]
            if len(set([audit[0] for audit in auditmessage])) < 3:
                judgeOrder += '@审批流程不规范'
                qualified_flag = 0
    elif orderTuple[
        21] == '成功':  # 2.如果 “完成情况  finishresult” 为 成功，则需要判断 工单流程是否闭环、割接阶段每个环节是否超时，割接结果反馈必须5点之前以及割接小组成员和审批流程是否 符合规定等
        if ncGrpMembers:
            # 割接小组成员  郑子健-监督审核人员@于永年-割接小组组长@吴宣东-网络监控人员@单晓芳-割接随工人员@单晓芳-割接操作人员@单晓芳-验证测试人员@闫彬-割接保障人员
            ncGrpMembers = ncGrpMembers.split('@')
            ncGrpMembers = [mem.split('-') for mem in ncGrpMembers]
            ncGrpMembersDict = {mem[1]: mem[0] for mem in ncGrpMembers}
            if len({ncGrpMembersDict['割接小组组长'], ncGrpMembersDict['割接操作人员'], ncGrpMembersDict['割接保障人员']}) < 3 or len(
                    {ncGrpMembersDict['监督审核人员'], ncGrpMembersDict['割接小组组长'], ncGrpMembersDict['网络监控人员'],
                     ncGrpMembersDict['割接随工人员'], ncGrpMembersDict['割接操作人员'], ncGrpMembersDict['验证测试人员'],
                     ncGrpMembersDict['割接保障人员']}) < 4:
                judgeOrder += '@割接小组成员不规范'
                qualified_flag = 0
        if auditmessage:
            # 审批流程     于永年-创建了割接工单-None@于永年-提交了割接方案-None@吴宣东-会审完成-@田东阳-审批完成-@杨尚涛-None-短信审批:同意
            auditmessage = auditmessage.split('@')
            auditmessage = [audit.split('-') for audit in auditmessage]
            if len(set([audit[0] for audit in auditmessage])) < 3:
                judgeOrder += '@审批流程不规范'
                qualified_flag = 0
        # 判断是否闭环 ，状态必须为割接完成
        if orderTuple[25] != '割接完成':
            judgeOrder += '@割接未闭环'
            qualified_flag = 0
        # 判断流程是否存在超时  以^符号间隔不同的阶段，每个阶段 先是计划时间，再是实际时间，以@间隔
        # 割接的各个环节时间点，分别是 割接准备、割接实施、测试验证、结果反馈、运行观察、割接完成  获取的是个列表
        pointTime = pointTime.split('^')
        pointTime = [item.split('@') for item in pointTime]  # 每个item是 预计开始时间，预计结束时间，实际开始时间，实际结束时间
        cutPrepare = pointTime[0]
        cutDo = pointTime[1]
        testValid = pointTime[2]
        resultReport = pointTime[3]
        operationObservation = pointTime[4]
        cutComplete = pointTime[5]
        # 如果实际开始时间早于计划开始时间，或者实际结束时间晚于预计结束时间，则算超时
        if cutPrepare[3] > cutPrepare[1]:
            # judgeOrder += '@割接准备环节时间不符合要求'
            judgeOrder += '@割接超时'
            qualified_flag = 0
        if cutDo[0] > cutDo[2] or cutDo[3] > cutDo[1]:
            # judgeOrder += '@割接实施环节时间不符合要求'
            judgeOrder += '@割接超时'
            qualified_flag = 0
        if testValid[0] > testValid[2] or testValid[3] > testValid[1]:
            # judgeOrder += '@测试验证环节时间不符合要求'
            judgeOrder += '@割接超时'
            qualified_flag = 0
        if resultReport[3] == '' or resultReport[3].split(' ')[1] > '05:00:00' or resultReport[3].split(' ')[
            1] == '':  # 结果反馈时间超过5点或者截至提取时间还没有反馈时间
            # judgeOrder += '@结果反馈环节时间超过5点'
            judgeOrder += '@割接超时'
            qualified_flag = 0
    return qualified_flag, judgeOrder


def analyzeOrders():
    """分析数据库中的工单"""
    global data_store
    # 1.如果工单未被分析过，则done这一列为no，对这一行做分析处理，同时将done列置为yes
    needs_analyze_orders = data_store.query_DB(f'select * from cutOverOrders where analyzeTime = "no"')  # 返回的是元组组成的列表
    # 2.分析这行数据，将此行数据的done列进行修改
    for item in needs_analyze_orders:
        qualified_flag, judgeOrder = orderQualified(item)
        orderSn = item[1]  # 获取工单编号
        data_store.opera_DB('update cutOverOrders set (analyzeTime,qualified, orderjudge) =(?,?,?)  where orderSn=?',
                            (time.strftime('%Y-%m-%d %H:%M:%S'), qualified_flag, judgeOrder, orderSn,))


def fillDailyReportTemplate(date=time.strftime('%Y-%m-%d')):
    """根据数据库中的表格自动填充报表的模板，完成每星期数据的自动分析，因为主要的功能是完成日报数据的填充，所以默认割接结束时间为当日"""
    global data_store, contact_dict, template_file,contact_dict2
    speciality_dict = {'传输网': '传输', '无线接入网': '无线', '动环': '动环', '虚拟化': "IT", '数据业务平台': 'IT', '核心网': '核心网', 'IDC': 'IT',
                       '内容网': 'IT', 'CMNET': 'IT', '核心网_5G专网': '核心网', '网管网': 'IT', '云平台': 'IT', 'IP承载网': 'IT',
                       '家宽': '家客', '安全': 'IT', '集客': '集客'}
    county_dict = {'铜山': '铜山',
                   '贾汪': '贾汪',
                   '丰县': '丰县',
                   '沛县': '沛县',
                   '邳州': '邳州',
                   '睢宁': '睢宁',
                   '新沂': '新沂',
                   '泉山': '市区',
                   '鼓楼': '市区',
                   '云龙': '市区',
                   '市区': '市区'
                   }
    result_list = []
    # 1.从数据库中提取数据，筛选条件为 割接结束时间为 date
    today_orders = data_store.query_DB(
        f'select * from cutOverOrders where substr(finishTime, 1, 10) = "{date}"')  # 返回的是元组组成的列表
    # 2.根据专业来进行
    if today_orders:
        with xw.App(add_book=False, visible=False) as app:
            # app = xw.App(add_book=False, visible=False)
            wb = app.books.open(template_file)
            sheet = wb.sheets['分地区割接汇总']
            sheet_list = wb.sheets['清单']
            sheet.activate()
            sheet.range('C2:K27').value = 0  # 要输入数据的地方全部置为0
            sheet_list.range('A2:AH1000').clear_contents()  # 清除清单数据
            order_list = []
            radio_count, trans_count, core_count, it_count, power_count = [0, 0, 0, 0, 0, 0, 0, 0], [0, 0, 0, 0, 0, 0,
                                                                                                     0, 0], [0, 0, 0, 0,
                                                                                                             0, 0, 0,
                                                                                                             0], [0, 0,
                                                                                                                  0, 0,
                                                                                                                  0, 0,
                                                                                                                  0,
                                                                                                                  0], [
                                                                              0, 0, 0, 0, 0, 0, 0, 0]
            for order in today_orders:
                # 进行工单判定，需要确定 1.工单区县；2、工单专业；3、工单质检情况
                order_speciality = speciality_dict.get(order[5])  # 工单所属专业
                order_county = county_dict.get(contact_dict.get(order[18])) if contact_dict.get(
                    order[18]) else contact_dict2.get(order_number_info(order[1])[3]) if contact_dict2.get(order_number_info(order[1])[3]) else '未匹配'  # 工单所属区县，默认通过割接人员的手机号码来匹配割接区县，因为通讯录不全有可能导致匹配失败的情况
                order_judge = list(set(order[-1].split('@')[1:])) if order[-1] != '' else []
                order_qualified_flag = order[-2]  # 如果1则合格，如果0则不合格
                if order_county == '市区':
                    if order_speciality == '无线':
                        radio_count[0] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K2').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J2').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[0] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K3').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J3').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                    elif order_speciality == '核心网':
                        core_count[0] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K4').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J4').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[0] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K5').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J5').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                    elif order_speciality == 'IT':
                        it_count[0] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K6').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J6').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '市区')
                            order_list.append(order_item)
                elif order_county == '铜山':
                    if order_speciality == '无线':
                        radio_count[1] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K7').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接流程不规范'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J7').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[1] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K8').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J8').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割小组不规范'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[1] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K9').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J9').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小区不规范'
                            order_item.insert(1, '铜山')
                            order_list.append(order_item)
                elif order_county == '贾汪':
                    if order_speciality == '无线':
                        radio_count[2] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K10').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J10').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[2] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K11').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J11').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[2] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K12').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J12').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '贾汪')
                            order_list.append(order_item)
                elif order_county == '丰县':
                    if order_speciality == '无线':
                        radio_count[3] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K13').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J13').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[3] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K14').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J14').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[3] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K15').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J15').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '丰县')
                            order_list.append(order_item)
                elif order_county == '沛县':
                    if order_speciality == '无线':
                        radio_count[4] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K16').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J16').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[4] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K17').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J17').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[4] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K18').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J18').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '沛县')
                            order_list.append(order_item)
                elif order_county == '邳州':
                    if order_speciality == '无线':
                        radio_count[5] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K19').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J19').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[5] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K20').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J20').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[5] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K21').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J21').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '邳州')
                            order_list.append(order_item)
                elif order_county == '睢宁':
                    if order_speciality == '无线':
                        radio_count[6] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K22').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J22').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小区不规范'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[6] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K23').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J23').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[6] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K24').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J24').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '睢宁')
                            order_list.append(order_item)
                elif order_county == '新沂':
                    if order_speciality == '无线':
                        radio_count[7] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K25').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J25').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                    elif order_speciality == '传输':
                        trans_count[7] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K26').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J26').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                    elif order_speciality == '动环':
                        power_count[7] += 1  # 割接总量加1
                        if [] == order_judge:
                            sheet.range('K27').value += 1
                        if '割接超时' in order_judge:
                            sheet.range('D27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接超时'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接未闭环' in order_judge:
                            sheet.range('E27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接未闭环'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接取消' in order_judge:
                            sheet.range('F27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接取消'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '非窗口期' in order_judge:
                            sheet.range('G27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '非窗口期'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接失败' in order_judge:
                            sheet.range('H27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接失败'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '审批流程不规范' in order_judge:
                            sheet.range('I27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '审批流程不规范'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
                        if '割接小组成员不规范' in order_judge:
                            sheet.range('J27').value += 1
                            order_item = list(order[:-6])
                            order_item[0] = '割接小组成员不规范'
                            order_item.insert(1, '新沂')
                            order_list.append(order_item)
            sheet.range('C2').value = radio_count[0]
            sheet.range('C3').value = trans_count[0]
            sheet.range('C4').value = core_count[0]
            sheet.range('C5').value = power_count[0]
            sheet.range('C6').value = it_count[0]
            sheet.range('C7').value = radio_count[1]
            sheet.range('C8').value = trans_count[1]
            sheet.range('C9').value = power_count[1]
            sheet.range('C10').value = radio_count[2]
            sheet.range('C11').value = trans_count[2]
            sheet.range('C12').value = power_count[2]
            sheet.range('C13').value = radio_count[3]
            sheet.range('C14').value = trans_count[3]
            sheet.range('C15').value = power_count[3]
            sheet.range('C16').value = radio_count[4]
            sheet.range('C17').value = trans_count[4]
            sheet.range('C18').value = power_count[4]
            sheet.range('C19').value = radio_count[5]
            sheet.range('C20').value = trans_count[5]
            sheet.range('C21').value = power_count[5]
            sheet.range('C22').value = radio_count[6]
            sheet.range('C23').value = trans_count[6]
            sheet.range('C24').value = power_count[6]
            sheet.range('C25').value = radio_count[7]
            sheet.range('C26').value = trans_count[7]
            sheet.range('C27').value = power_count[7]
            for i in "CEDFGHIJK":
                for j in range(2, 28):
                    if sheet.range(f'{i}{j}').value == 0:
                        sheet.range(f'{i}{j}').clear_contents()
            sheet_list.range('A2').value = order_list
            result_list = sheet.range('C28:K28').value
            result_list = [int(num) for num in result_list]
            wb.save(f"{report_generate}徐州割接统计{time.strftime('%Y%m%d')}.xlsx")
            wb.close()
        write_log('今天徐州割接统计日报已生成，请到对应目录下查看')
        sendMail(password=email_address[1], mail_domain=email_address[2], sender_tuple=('指标自动报送邮箱', email_address[0]),
                 receivers_tuples=[('赵桂兰','zhaogl@js.chinamobile.com'),('张尧', 'zhangyaoxz@js.chinamobile.com'),('王秋兰', 'wangql@js.chinamobile.com'),('徐磊', 'xuleiXZ@js.chinamobile.com'),('监控1','13951350574@139.com'),('监控2','13951464014@139.com'),('监控3','13952200442@139.com'),('监控4','14705207547@139.com'),('白维','baiwei0327@139.com')],
                 subject=f'集中调度平台第{time.strftime("%Y%m%d")}期自动核查结果，请收阅！该日报由机器人自动生成，详见正文，如有问题，请反馈开发人员！',
                 body=f"{time.strftime('%Y{y}%m{m}%d{d}').format(y='年', m='月', d='日')}" + f'徐州工程割接情况，割接总量为{result_list[0]}例,其中割接超时{result_list[1]}例，割接未闭环{result_list[2]}例，割接取消{result_list[3]}例，非窗口期{result_list[4]}例，割接失败{result_list[5]}例，审批流程不规范{result_list[6]}例，割接小组成员不规范{result_list[7]}例，割接成功{result_list[8]}例。'+"\n更多内容详见附件",
                 attachment=os.path.abspath(f"{report_generate}徐州割接统计{time.strftime('%Y%m%d')}.xlsx"))
    else:
        write_log("今天徐州没有工程割接，无文件生成。")
        sendMail(password=email_address[1], mail_domain=email_address[2], sender_tuple=('指标自动报送邮箱', email_address[0]),
                 receivers_tuples=[('赵桂兰','zhaogl@js.chinamobile.com'),('张尧', 'zhangyaoxz@js.chinamobile.com'),('王秋兰', 'wangql@js.chinamobile.com'),('徐磊', 'xuleiXZ@js.chinamobile.com'),('监控1','13951350574@139.com'),('监控2','13951464014@139.com'),('监控3','13952200442@139.com'),('监控4','14705207547@139.com'),('白维','baiwei0327@139.com')],
                 subject=f'集中调度平台第{time.strftime("%Y%m%d")}期自动核查结果，请收阅！该日报由机器人自动生成，详见正文，如有问题，请反馈开发人员！',
                 body=f"{time.strftime('%Y{y}%m{m}%d{d}').format(y='年', m='月', d='日')}" + '徐州没有工程割接，无文件生成，请阅知。',
                 attachment='')


def sendMail(password='', sender_tuple=('张尧', 'zhangyaoxz@js.chinamobile.com'),
             receivers_tuples=[('张尧', 'zhangyaoxz@js.chinamobile.com')], mail_domain='', subject='', body='',
             attachment=''):
    """这个一个自定义的邮件发送类"""
    sender = sender_tuple[1]
    receivers = [receiver[1] for receiver in receivers_tuples]

    message = MIMEMultipart()
    message['From'] = Header(f"{sender_tuple[0]}<{sender_tuple[1]}>", 'utf-8')
    #     message['To'] =  Header(f"{receivers_tuples[0]}<{}>", 'utf-8')
    message['Subject'] = Header(subject, 'utf-8')

    # 邮件正文内容
    message.attach(MIMEText(body, 'plain', 'utf-8'))

    # 构造附件1，传送当前目录下的 test.txt 文件
    if attachment == '':
        pass
    else:
        attach_name = attachment.split(os.sep)[-1]
        attachment_content = ''
        with open(attachment,'rb') as f:
            attachment_content = f.read()
        att1 = MIMEText(attachment_content, 'base64', 'utf-8')
        att1["Content-Type"] = 'application/octet-stream'
        # 这里的filename可以任意写，写什么名字，邮件中显示什么名字
        att1.add_header('Content-Disposition', 'attachment', filename=f'{attach_name}')
        message.attach(att1)
    try:
        smtpObj = smtplib.SMTP_SSL(mail_domain, 465)
        smtpObj.login(sender_tuple[1], password)
        for receiver in receivers_tuples:
            message['To'] = Header(f"{receiver[0]}<{receiver[1]}>", 'utf-8')
            smtpObj.sendmail(sender, receiver[1], message.as_string())
        write_log("邮件发送成功")
    except smtplib.SMTPException as e:
        write_log(f"Error: 无法发送邮件:{e}")

def job():
    """main函数"""
    global account_info
    try:
        init_environment()  # 初始化环境
        login_final_fun(account_info[0], account_info[1])
        futchAndStoreDatas()  # 从网站上提取数据同时保存到本地数据库
        analyzeOrders()  # 对数据库中的数据进行基本的分析并保存数据
        fillDailyReportTemplate()  # 对当日的割接数据做出统计
        return True
    except Exception as e:
        write_log(e)
        return False



if __name__ == "__main__":
    job()




